package jnpf.database.source.impl;

import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import jnpf.base.DbTableModel;
import jnpf.database.data.DataSourceContextHolder;
import jnpf.database.enums.DataTypeEnum;
import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.DbSttEnum;
import jnpf.database.source.DbBase;
import jnpf.database.model.DbTableFieldModel;
import jnpf.database.model.dto.DbTableDTO;
import jnpf.database.util.DbUtil;
import jnpf.util.JdbcUtil;
import jnpf.util.StringUtil;
import org.apache.commons.collections4.CollectionUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class DbKingbase extends DbBase {

    @Override
    public void setDbType() {
        dbEncode = "KingBase";
        driver = "com.kingbase8.Driver";
        fieldSql =
            "SELECT DISTINCT\n"+
            "c.relname,\n"+
            "a.attnum,\n"+
            "a.attname AS "+DbAliasEnum.FIELD_NAME.AS()+",\n"+
            "t.typname AS "+DbAliasEnum.DATA_TYPE.AS()+",\n"+
            "a.attnotnull AS "+DbAliasEnum.ALLOW_NULL.AS()+",\n"+
            "b.character_maximum_length AS "+DbAliasEnum.DATA_LENGTH.AS()+",\n"+
            "d.COMMENTS AS "+DbAliasEnum.FIELD_COMMENT.AS()+",\n"+
            "pk.colname AS "+DbAliasEnum.PRIMARY_KEY.AS()+"\n"+
            "FROM sys_class c\n"+
            "INNER JOIN sys_namespace n on c.relnamespace = n.oid\n"+
            "INNER JOIN sys_attribute a ON c.oid = a.attrelid\n"+
            "INNER JOIN information_schema.columns b ON c.relname = b.table_name\n"+
            "INNER JOIN DBA_COL_COMMENTS d on a.attname = d.column_name\n"+
            "INNER JOIN sys_type t ON a.atttypid = t.oid\n"+
            "LEFT JOIN\n"+
            "(SELECT\n"+
            "c.conname AS pk_name,\n"+
            "a.attname AS colname\n"+
            "FROM\n"+
            "sys_constraint AS c\n"+
            "INNER JOIN sys_class AS clz ON c.conrelid = clz.oid\n"+
            "INNER JOIN sys_attribute AS a ON a.attrelid = clz.oid\n"+
            "AND a.attnum = c.conkey [1]\n"+
            "WHERE\n"+
            "clz.relname = '"+ DbSttEnum.TABLE.getTarget()+"'\n"+
            "AND c.contype = 'p') AS pk ON a.attname = pk.colname\n"+
            "WHERE attnum > 0\n"+
            "AND b.column_name = a.attname\n"+
            "AND c.relname = d.table_name\n"+
            "AND nspname = '"+ DbSttEnum.DB_SCHEMA.getTarget()+"'\n"+
            "AND c.relname = '"+ DbSttEnum.TABLE.getTarget()+"'\n"+
            "ORDER BY attnum;";
        listSql =
                "SELECT t.TABLE_NAME AS "+DbAliasEnum.TABLE_NAME.AS()+",c.COMMENTS AS "+DbAliasEnum.TABLE_COMMENT.AS()+" FROM\n" +
                "information_schema.TABLES AS t\n" +
                "LEFT JOIN\n" +
                "(SELECT TABLE_NAME,COMMENTS FROM DBA_TAB_COMMENTS)AS c\n" +
                "ON\n" +
                "t.TABLE_NAME = c.TABLE_NAME\n" +
                "\n" +
                "WHERE\n" +
                " TABLE_SCHEMA = '"+ DbSttEnum.DB_SCHEMA.getTarget()+"'";


    }

    @Override
    public String getDataType(DataTypeEnum dte){
        return dte.getKingbaseFieldType();
    }

    @Override
    public String getConnectionUrl(String host, String port, String dbName) {
        return "jdbc:kingbase8://"+ host +":"+ port +"/"+ dbName;
    }

    @Override
    public void setDynamicMap(HashMap<String, TableNameHandler> map, DbTableModel dbTableModel) {
        map.put(dbTableModel.getTable(), (sql, tableName) -> DataSourceContextHolder.getDatasourceName()+"."+dbTableModel.getTable());
    }

    @Override
    public DbTableModel getTableModel(ResultSet result) throws SQLException {
        return DbUtil.getTableModelCommon(result,
                true,
                true,
                true,
                false,
                false,
                false
        );
    }

    @Override
    public DbTableFieldModel getFieldModel(ResultSet result, Map<String, Object> map) throws SQLException {
        DbTableFieldModel model = new DbTableFieldModel();
        //主键
        if(result.getString(DbAliasEnum.PRIMARY_KEY.AS()) != null){
            model.setPrimaryKey(DbAliasEnum.PRIMARY_KEY.isTrue());
        }else {
            model.setPrimaryKey(DbAliasEnum.PRIMARY_KEY.isFalse());
        }
        //允空
        if(result.getBoolean(DbAliasEnum.ALLOW_NULL.AS())){
            model.setAllowNull(DbAliasEnum.ALLOW_NULL.isFalse());
        }else {
            model.setAllowNull(DbAliasEnum.ALLOW_NULL.isTrue());
        }
        return DbUtil.getFieldModelCommon(result, this,model);
    }

    @Override
    public void createTable(DbTableDTO dto) {
        /**
         * 语法解析：
         *CREATE
         *     TABLE
         *         "${模式名}".${表名}(
         *             ${字段名} BIGINT(${精度}, ${标度}) VARCHAR(${长度}) NOT NULL $/为空则去掉/ SERIAL $/自增/ DEFAULT '${默认值}',
         *             ...
         *             CONSTRAINT "${表名}_PKEY" PRIMARY KEY ({字段名})    //设置主键
         *         ) TABLESPACE "${表空间}";
         *
         * COMMENT ON
         * TABLE "${模式名}".${表名} IS '${表注解}';
         * COMMENT ON
         * COLUMN "${模式名}".${表名}.${字段名} IS '${字段注解}';
         * （精度：长度）
         * （标度：小数点）
         */
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE "+ dto.getNewTable() + "( ");

        //默认主键为字符串类型
        //TODO 多个主键未处理
        dto.getDbTableFieldList().stream().filter(t->t.getPrimaryKey()==1).collect(Collectors.toList()).get(0).setDataType("VARCHAR");
        for (DbTableFieldModel item : dto.getDbTableFieldList()) {
            sql.append(item.getField() + " " + item.getDataType() + " ");
            //添加字段长度(设置多个类型的判断)
            if ("VARCHAR".equals(item.getDataType()) || "DECIMAL".equals(item.getDataType())) {
                //只设置精度（长度），不设置标度（小数点），这个功能待完善。
                sql.append("(" + item.getDataLength() + ") ");
            }
            //非空判断
            if ("1".equals(String.valueOf(item.getAllowNull()))) {
                sql.append(" NOT NULL ");
            }
            //默认值
            if(StringUtil.isNotNull(item.getDefaults())){
                sql.append("DEFAULT '"+item.getDefaults()+"',");
            }else {
                sql.append(",");
            }
        }
        //添加主键
        //目前只能设置一个主键，多个主键不支持
        List<DbTableFieldModel> dbTableFieldModelList =  dto.getDbTableFieldList().stream().filter(t->t.getPrimaryKey()==1).collect(Collectors.toList());
        if(CollectionUtils.isNotEmpty(dbTableFieldModelList)){
            sql.append("CONSTRAINT \""+dto.getNewTable()+"_PKEY\" PRIMARY KEY ("+dbTableFieldModelList.get(0).getField()+"),");
        }

        //删除逗号
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") TABLESPACE \"" + dto.getTableSpace() + "\";");
        //字段注释
        for(DbTableFieldModel item : dto.getDbTableFieldList()){
            sql.append("COMMENT ON COLUMN "+dto.getNewTable()+ "."+item.getField() +" IS '"+item.getFieldComment()+"';");
        }
        //表注释
        if(StringUtil.isNotNull(dto.getTableComment())){
            sql.append("COMMENT ON TABLE  "+dto.getNewTable()+" IS '"+dto.getTableComment()+"';");
        }
        JdbcUtil.custom(dto.getConn(),sql.toString());
    }

    @Override
    public void updateTable(DbTableDTO dto) {
        StringBuffer sql = new StringBuffer();
        //先删除表，再创建表
        sql.append("DROP TABLE \""+dto.getOldTable()+"\";");
        //金仓的表名默认均为大写
        dto.setNewTable(dto.getNewTable().toUpperCase());
        sql.append("CREATE TABLE ."+dto.getNewTable()+
                "() TABLESPACE "+"\""+ dto.getTableSpace() +"\";");
        //表定位
        String from = " \""+dto.getNewTable()+"\"";
        String tableFrom = "TABLE "+ from;

        /**====================修改表信息========================**/
        //当旧表名与新表名不同时候，说明对表名进行了修改
        /*if(!dto.getNewTable().equals(dto.getOldTable())){
            //修改表名
            sql.append("ALTER TABLE \""+dto.getDbSchema()+"\".\""+dto.getOldTable()+"\" RENAME TO \""+dto.getNewTable()+"\";");
        }*/
        //修改表注释
        if(StringUtil.isNotNull(dto.getTableComment())){
            sql.append("COMMENT ON "+tableFrom+" IS '"+dto.getTableComment()+"';");
        }
        /*if(StringUtil.isNotNull(dfm.getNewField())){
            //修改字段名
            sql.append("ALTER "+tableFrom+" RENAME COLUMN \""+dfm.getOldField()+"\" TO "+dfm.getNewField()+";");
        }else {
            dfm.setNewField(dfm.getField());
        }*/
        /**====================修改字段信息========================**/
        /*for(DbTableFieldModel dfm : dto.getDbTableFieldList()){
            //字段修改通用
            dfm.setNewField(dfm.getField());
            //由于一些字段类型一旦创建无法改变，于是先删除再创建
            sql.append(" ALTER "+tableFrom+" DROP COLUMN \""+dfm.getNewField()+"\";");
        }*/
        for(DbTableFieldModel dfm : dto.getDbTableFieldList()){
            //字段修改通用，金仓自动转换大写字段名
            dfm.setNewField(dfm.getField().toUpperCase());
            String alterField = tableFrom + "ALTER COLUMN \""+dfm.getNewField()+"\"";
            //添加字段类型

            //添加长度
            String datalength = "";
            if(StringUtil.isNotNull(dfm.getDataLength())) {
                //判断字符串是否是整型
                if (Pattern.compile("^[-\\+]?[\\d]*$").matcher(dfm.getDataLength()).matches() &&
                        DataTypeEnum.VARCHAR.getCommonFieldType().equals(dfm.getDataType())){
                    //目前除了Varchar其他类型修改会失败，原因不明
                    datalength = "("+dfm.getDataLength()+")";
                }
            }
            //类型为空，默认给varchar值
            if(StringUtil.isNull(dfm.getDataType())){
                dfm.setDataType(DataTypeEnum.VARCHAR.getCommonFieldType());
            }
            //添加列类型（长度）
            sql.append("ALTER "+tableFrom+" ADD COLUMN "+
                    dfm.getNewField()+" "+ DataTypeEnum.getDbFieldType(dfm.getDataType(),this)+ datalength+";");
            /*sql.append("ALTER "+alterField+" TYPE "+DataTypeEnum.getDbFieldType(dfm.getDataType(),DbEnum.KING_BASE)+";");*/
            //修改字段长度（精度）

            //修改字段注解
            if(StringUtil.isNotNull(dfm.getFieldComment())){
                sql.append("COMMENT ON COLUMN "+ from +".\""+dfm.getNewField()+"\" IS '"+dfm.getFieldComment()+"';");
            }
            //修改允空
            if(dfm.getAllowNull()!=null){
                //允许空
                if(DbAliasEnum.ALLOW_NULL.isTrue().equals(dfm.getAllowNull())){
                    sql.append("ALTER "+alterField+" DROP NOT NULL;");
                }else {
                    sql.append("ALTER "+alterField+" SET  NOT NULL;");
                }
            }
            if(DbAliasEnum.PRIMARY_KEY.isTrue().equals(dfm.getPrimaryKey())){
                //先删除主键再添加
                /*sql.append("ALTER "+tableFrom+" DROP CONSTRAINT \"PRIMARY_KEY\";");*/
                sql.append( "ALTER "+tableFrom+" ADD CONSTRAINT "+dto.getNewTable()+"_KEY PRIMARY KEY("+dfm.getNewField()+");");
            }
            //修改默认值
            if(StringUtil.isNotNull(dfm.getDefaults())){
                sql.append("ALTER TABLE "+alterField+" SET DEFAULT '"+dfm.getDefaults()+"';");
            }
        }
        JdbcUtil.custom(dto.getConn(), sql.toString());


    }

    @Override
    public String getDbTime(Connection conn) {
        return null;
    }

    @Override
    public String getSqlByStructure(String sql,Connection conn,String table,String tableSpace) {
        /**
         * 说明：一个实例对应多个数据库，一个数据库对应多个模式，一个用户对应一个模式
         * 数据库 = dbName;
         * 模式 = schema = userName;
         * 表空间需默认：JNPF
         */

        return sql.
                replace(DbSttEnum.TABLE.getTarget(),table).
                replace(DbSttEnum.DB_NAME.getTarget(), DbSttEnum.DB_NAME.getValByConn(conn)).
                replace(DbSttEnum.DB_SCHEMA.getTarget(),DbSttEnum.USER_NAME.getValByConn(conn)).
                replace(DbSttEnum.TABLE_SPACE.getTarget(),tableSpace);
    }

}
